﻿CREATE PROCEDURE [dbo].[sp_Get_OrderInfo_New]
	@Month int,
	@Year int
AS
	select os.Division, os.Market, a.FirstName + ' ' + a.LastName, 
	os.ActiveCount, os.LapsedCount, os.NeighborCount, os.BusinessCount, os.UpdateTS, 
	case when dm.AgentCount is not null then dm.AgentCount else a.AgentNumber end,
	(select count(*) from Agents where mdnumber = a.AccountID and Territory is not null) as AlphaRosterCount,
	(select COUNT(distinct substring(AgentName, CHARINDEX(' ', AgentName) + 1, len(agentname))) 
	from ZipInfo where MdNumber = a.AccountID and AgentName is not null
	and substring(AgentName, CHARINDEX(' ', AgentName) + 1, len(AgentName)) in (select name from Agents where mdnumber = a.AccountID and Territory is not null)) as AlphaRosterOrderCount,
	(select count(*) from Agents where mdnumber = a.AccountID and Territory is null) as CreateAgentCount,
	(select COUNT(distinct substring(AgentName, CHARINDEX(' ', AgentName) + 1, len(AgentName))) 
	from ZipInfo where MdNumber = a.AccountID and AgentName is not null
	and substring(AgentName, CHARINDEX(' ', AgentName) + 1, len(AgentName)) not in (select name from Agents where mdnumber = a.AccountID and Territory is not null)) as CreatedAgentOrderCount,
	(select COUNT(distinct AgentName) from ZipInfo where MdNumber = a.AccountID and AgentName is not null) as TotalAssignments,
	os.ActiveLeadCount, os.LapsedLeadCount, os.AccountID
	from 
		(select zi.Division, zi.Market, o.AccountID, MAX(o.UpdateTS) as UpdateTS, 
		sum(case when oz.Type = 2 then 0 else zi.ActiveStopCount end) as ActiveLeadCount,
		sum(case when oz.Type = 2 then 0 else zi.LapsedStopCount end) as LapsedLeadCount,
		sum(case when oz.Type = 2 then 0 else zi.ActiveLeadCount end) as ActiveCount,
		sum(case when oz.Type = 2 then 0 else zi.LapsedLeadCount end) as LapsedCount, 
		sum(case when oz.Type = 2 then 0 else zi.NeighborLeadCount end) as NeighborCount,
		sum(case when oz.Type = 1 then 0 else zi.BusinesLeadCount end) as BusinessCount
		from dbo.Orders o
		inner join dbo.OrderZip oz on o.OrderID = oz.OrderID
		and o.PeroidYear =@Year and o.PeroidMonth =@Month and o.IsPurchased = 1
		inner join dbo.ZipInfo zi on zi.ZipID = oz.ZipID
		group by o.OrderID, o.AccountID, zi.Division, zi.Market)os
	inner join dbo.Accounts a on os.AccountID = a.AccountID and a.Status <> 2
	left join dbo.DivisionMarket dm on os.AccountID = dm.MdNumber 
	and os.Division = dm.Division and os.Market = dm.Market
	order by os.AccountID